﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;

namespace EVideoDataService
{
  public  class VideoInfoService
    {
        /// <summary>
        /// 通过开始事件获得指定的VFINFO
        /// </summary>
        /// <param name="datetime">开始时间</param>
        /// <returns></returns>
        public VFInfo GetVSInfoByDateTime(int schoolID, int roomID, DateTime datetime, VFType vfType)
        {
            VFInfo vfInfo = new VFInfo();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["VideoConnString"].ConnectionString))
            {
                StringBuilder commString = new StringBuilder();
                commString.Append("SELECT VFIT.VFName,VFIT.VFUrl,VFIT.VFYear,VFIT.VFMonth,VFIT.VFDay,VFIT.VFHour,VFIT.VFMinute,VPIT.VPUrl");
                commString.Append(" FROM [dbo].[VideoFormatInfo_Table] VFIT");
                commString.Append(" INNER JOIN [dbo].[S_R_VS_Relation_Table] SRVSRT ON VFIT.VSID = SRVSRT.VSID");
                commString.Append(" INNER JOIN [dbo].[VF_VP_Relation_Table] VFVPRT ON VFVPRT.VFID = VFIT.VFID");
                commString.Append(" INNER JOIN [dbo].[VideoPictureInfo_Table] VPIT ON VPIT.VPID = VFVPRT.VPID");
                commString.Append(" WHERE VFIT.VFYear =" + datetime.Year);
                commString.Append(" and VFIT.VFMonth =" + datetime.Month);
                commString.Append(" and VFIT.VFDay =" + datetime.Day);
                commString.Append(" and VFIT.VFHour =" + datetime.Hour);
                commString.Append(" and VFIT.VFMinute >=" + (datetime.Minute - 20) + " and VFIT.VFMinute <" + datetime.Minute);
                commString.Append(" and SRVSRT.[SID] =" + schoolID);
                commString.Append(" and SRVSRT.[RID] =" + roomID);

                using (SqlCommand comm = new SqlCommand())
                {
                    comm.CommandText = commString.ToString();
                    comm.Connection = conn;
                    conn.Open();

                    using (SqlDataReader sdr = comm.ExecuteReader())
                    {
                        if (sdr.Read())
                        {
                            vfInfo.VFName = sdr["VFName"].ToString();
                            vfInfo.VFUrl = this.GetHTTPVFUrl(sdr["VFUrl"].ToString() + "_" + vfType.ToString() + ".flv");
                            vfInfo.VFYear = int.Parse(sdr["VFYear"].ToString());
                            vfInfo.VFMonth = int.Parse(sdr["VFMonth"].ToString());
                            vfInfo.VFDay = int.Parse(sdr["VFDay"].ToString());
                            vfInfo.VFHour = int.Parse(sdr["VFHour"].ToString());
                            vfInfo.VFMinute = int.Parse(sdr["VFMinute"].ToString());

                            VPInfo vpInfo = new VPInfo();
                            vpInfo.VPUrl = this.GetHTTPVPUrl(sdr["VPUrl"].ToString() + ".jpg");
                            vfInfo.vpInfo = vpInfo;
                        }
                    }
                }
            }

            return vfInfo;
        }

        /// <summary>
        /// 通过时间段获得VFINFO列表
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<VFInfo> GetVSInfoListByTimespan(int schoolID, int roomID, DateTime startTime, DateTime endTime, VFType vfType)
        {
            List<VFInfo> list = new List<VFInfo>();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["VideoConnString"].ToString()))
            {
                StringBuilder commString = new StringBuilder();
                commString.Append("SELECT VFIT.VFName,VFIT.VFUrl,VFIT.VFYear,VFIT.VFMonth,VFIT.VFDay,VFIT.VFHour,VFIT.VFMinute,VFIT.VSTime,VPIT.VPUrl,VFIT.VSID");
                commString.Append(" FROM [dbo].[VideoFormatInfo_Table] VFIT");
                commString.Append(" INNER JOIN [dbo].[S_R_VS_Relation_Table] SRVSRT ON VFIT.VSID = SRVSRT.VSID");
                commString.Append(" INNER JOIN [dbo].[VF_VP_Relation_Table] VFVPRT ON VFVPRT.VFID = VFIT.VFID");
                commString.Append(" INNER JOIN [dbo].[VideoPictureInfo_Table] VPIT ON VPIT.VPID = VFVPRT.VPID");
                commString.Append(" WHERE VFIT.[VSTime] >='" + startTime.ToString() + "'");
                commString.Append(" and VFIT.[VSTime] <='" + endTime.ToString() + "'");
                commString.Append(" and SRVSRT.[SID] =" + schoolID);
                commString.Append(" and SRVSRT.[RID] =" + roomID);

                using (SqlCommand comm = new SqlCommand())
                {
                    comm.CommandText = commString.ToString();
                    comm.Connection = conn;
                    conn.Open();

                    using (SqlDataReader sdr = comm.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            VFInfo vfInfo = new VFInfo();

                            vfInfo.VFName = sdr["VFName"].ToString();
                            vfInfo.VFUrl = this.GetHTTPVFUrl(sdr["VFUrl"].ToString() + "_" + vfType.ToString() + ".flv");
                            vfInfo.VSTime = Convert.ToDateTime(sdr["VSTime"].ToString());
                            vfInfo.VFYear = int.Parse(sdr["VFYear"].ToString());
                            vfInfo.VFMonth = int.Parse(sdr["VFMonth"].ToString());
                            vfInfo.VFDay = int.Parse(sdr["VFDay"].ToString());
                            vfInfo.VFHour = int.Parse(sdr["VFHour"].ToString());
                            vfInfo.VFMinute = int.Parse(sdr["VFMinute"].ToString());
                            vfInfo.VSID = int.Parse(sdr["VSID"].ToString());
                            VPInfo vpInfo = new VPInfo();
                            vpInfo.VPUrl = this.GetHTTPVPUrl(sdr["VPUrl"].ToString() + ".jpg");
                            vfInfo.vpInfo = vpInfo;

                            list.Add(vfInfo);
                        }
                    }
                }
            }

            return list;
        }

        /// <summary>
        /// 获得HTTPurl地址
        /// </summary>
        /// <param name="vfUrl"></param>
        /// <returns></returns>
        public string GetHTTPVFUrl(string vfUrl)
        {
            string httpVFUrl = vfUrl.Replace(ConfigurationManager.AppSettings["FileSavePath"].ToString(), ConfigurationManager.AppSettings["ServerName"].ToString()).Replace(@"\", "/");

            return httpVFUrl;
        }
        /// <summary>
        /// 获得HTTPvpurl
        /// </summary>
        /// <param name="vpUrl"></param>
        /// <returns></returns>
        public string GetHTTPVPUrl(string vpUrl)
        {
            string httpVPUrl = vpUrl.Replace(ConfigurationManager.AppSettings["PictureSavePath"].ToString(), ConfigurationManager.AppSettings["PictureServerName"].ToString()).Replace(@"\", "/");

            return httpVPUrl;
        }
    }
}
